package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.UserDao;
import model.UserModel;
import util.C3p0Util;
import util.DBUtil;

public class UserDaoImpl implements UserDao{

	@Override
	public UserModel findByUP(UserModel userModel) throws Exception {
		Connection connection= DBUtil.getconnection();
		PreparedStatement ps = connection.prepareStatement("select * from usermodel where username=? and password=?");
		ps.setString(1,userModel.getUsername());
		ps.setString(2,userModel.getPassword());
		ResultSet rs = ps.executeQuery();
		UserModel userModel2=null;
		if(rs.next()){
			userModel2=new UserModel();
			userModel2.setUsername(rs.getString(1));
			userModel2.setPassword(rs.getString(2));
		}
		rs.close();
		ps.close();
		connection.close();
		return userModel2;
	}

	@Override
	public void add(UserModel userModel) throws Exception {
		Connection connection= DBUtil.getconnection();
		PreparedStatement ps = connection.prepareStatement("insert into usermodel values(?,?,?,?,?,?,?)");
		ps.setString(1,userModel.getUsername());
		ps.setInt(2,userModel.getPowernum());
		ps.setString(3,userModel.getPassword());
		ps.setString(4,userModel.getGender());
		ps.setString(5,userModel.getPhone());
		ps.setString(6,userModel.getUserid());
		ps.setString(7,userModel.getEmail());
		
		ps.executeUpdate();
		ps.close();
		connection.close();
	}

	@Override
	public List<UserModel> findByName(String username) throws SQLException {
		
		return C3p0Util.qr.query("select * from usermodel where username='"+username+"'",new BeanListHandler<UserModel>(UserModel.class));
	}

	@Override
	public UserModel modifyByP(UserModel userModel,String oldpassword) throws Exception {
		Connection connection=DBUtil.getconnection();
		PreparedStatement ps = connection.prepareStatement("UPDATE usermodel SET password=? WHERE username=? and password=?");
		ps.setString(1,userModel.getPassword());
		ps.setString(2,userModel.getUsername());
		ps.setString(3,oldpassword);
		ps.executeUpdate();
		ps.close();
		connection.close();
		return userModel;
	}

	@Override
	public UserModel modifyByM(UserModel userModel) throws Exception {
		Connection connection=DBUtil.getconnection();
		PreparedStatement ps = connection.prepareStatement("UPDATE usermodel SET gender=?,phone=?,userid=?,email=? WHERE username=?");
		ps.setString(1,userModel.getGender());
		ps.setString(2,userModel.getPhone());
		ps.setString(3,userModel.getUserid());
		ps.setString(4,userModel.getEmail());
		ps.setString(5,userModel.getUsername());
		ps.executeUpdate();
		ps.close();
		connection.close();
		return userModel;
	}

}
